package dao;

import model.Doctor;
import util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class DoctorDao {
    public static void insert(Doctor model)
    {
        try
        {
            Connection conn= DbUtil.getConnection();
            String sql="insert into doctor(id,name,password,sex,deptId)";
            sql +="values(?,?,?,?,?);";
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1, model.getId());
            pstmt.setString(2, model.getName());
            pstmt.setString(3, model.getPassword());
            pstmt.setInt(4, model.getSex());
            pstmt.setInt(5, model.getDeptId());
            pstmt.executeUpdate();
            DbUtil.release(pstmt, conn);
        } catch (ClassNotFoundException | SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void update(Doctor model)
    {
        try
        {
            Connection conn=DbUtil.getConnection();
            String sql="update doctor set name = ?, password = ?, sex = ?, deptId = ?";
            sql +=" where id = ?;";
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setString(1, model.getName());
            pstmt.setString(2, model.getPassword());
            pstmt.setInt(3, model.getSex());
            pstmt.setInt(4, model.getDeptId());
            pstmt.setInt(5, model.getId());
            pstmt.executeUpdate();
            DbUtil.release(pstmt, conn);
        } catch (ClassNotFoundException | SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void delete(Integer id)
    {
        try
        {
            Connection conn=DbUtil.getConnection();
            String sql="delete from doctor where id = ?;";
            PreparedStatement pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.executeUpdate();
            DbUtil.release(pstmt, conn);
        } catch (ClassNotFoundException | SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static List<Doctor> select(Integer id, String name, String password, Integer sex, Integer deptId)
    {
        try
        {
            String sql="select t.id as id, t.name as name, t.password as password, ";
            sql += "t.sex as sex, t.deptId as deptId, d.name as dept_name ";
            sql += "from doctor t ";
            sql += "left join dept d ";
            sql += "on t.deptId = d.id ";
            sql += "where 1=1 ";
            if(id != null)
            {
                sql += "and t.id = '"+id+"' ";
            }
            if(name != null)
            {
                sql += "and t.name = '"+name+"' ";
            }
            if(password != null)
            {
                sql += "and t.password = '"+password+"' ";
            }
            if(sex != null)
            {
                sql += "and t.sex  = '"+sex +"' ";
            }
            if(deptId != null)
            {
                sql += "and t.deptId = '"+deptId+"' ";
            }
            sql +=";";
            return fillResult(sql);
        } catch (ClassNotFoundException | SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return new LinkedList<>();
        }
    }

    private static List<Doctor> fillResult(String sql) throws ClassNotFoundException, SQLException
    {
        List<Doctor> doctors = new LinkedList<>();
        Connection conn = DbUtil.getConnection();
        PreparedStatement pstmt=conn.prepareStatement(sql);
        ResultSet rs= pstmt.executeQuery();
        while(rs.next())
        {
            Doctor doctor = new Doctor(rs.getInt("id"),rs.getString("name"),rs.getString("password"),
                    rs.getInt("sex"),rs.getInt("deptId"),rs.getString("dept_name"));
            doctors.add(doctor);
        }
        DbUtil.release(pstmt, conn);
        return doctors;
    }
}
